﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using ModelLayer;

namespace DBLayer
{
    public class MovieDBClient : IMovieDB
    {
        private static SqlCommand dbCmd = null;

        public Movie GetMovie(int id)
        {
            string sql = "SELECT * FROM Movie WHERE id = " + id + ";";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Movie movie;

            if (dbReader.Read())
            {
                movie = new Movie(
                    Convert.ToInt32(dbReader["id"].ToString()),
                    Convert.ToBoolean(dbReader["active"].ToString()),
                    dbReader["title"].ToString(),
                    Convert.ToInt32(dbReader["playLength"].ToString()),
                    dbReader["actors"].ToString(),
                    Convert.ToInt32(dbReader["minAge"].ToString()),
                    dbReader["description"].ToString(),
                    dbReader["rating"].ToString(),
                    dbReader["director"].ToString(),
                    dbReader["genre"].ToString(),
                    dbReader["language"].ToString(),
                    Convert.ToDecimal(dbReader["price"].ToString()),
                    dbReader["licencee"].ToString(),
                    Convert.ToDateTime(dbReader["rentalDateStart"]),
                    Convert.ToDateTime(dbReader["rentalDateEnd"]));
            }
            else
            {
                movie = null;
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return movie;
        }

        public Movie GetMovie(string title)
        {
            string sql = "SELECT * FROM Movie WHERE title = '" + title + "';";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Movie movie;

            if (dbReader.Read())
            {
                movie = new Movie(
                    Convert.ToInt32(dbReader["id"].ToString()),
                    Convert.ToBoolean(dbReader["active"].ToString()),
                    dbReader["title"].ToString(),
                    Convert.ToInt32(dbReader["playLength"].ToString()),
                    dbReader["actors"].ToString(),
                    Convert.ToInt32(dbReader["minAge"].ToString()),
                    dbReader["description"].ToString(),
                    dbReader["rating"].ToString(),
                    dbReader["director"].ToString(),
                    dbReader["genre"].ToString(),
                    dbReader["language"].ToString(),
                    Convert.ToDecimal(dbReader["price"].ToString()),
                    dbReader["licencee"].ToString(),
                    Convert.ToDateTime(dbReader["rentalDateStart"]),
                    Convert.ToDateTime(dbReader["rentalDateEnd"])
                );
            }
            else
            {
                movie = null;
            }
            dbReader.Close();
            dbCmd.Connection.Close();

            return movie;
        }

        public List<Movie> GetMovies(DateTime dateTime)
        {
            string sql = "SELECT * FROM Movie WHERE rentalDateStart <= CAST('" + dateTime.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime) AND rentalDateEnd >= CAST('" + dateTime.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime) ORDER BY id ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            List<Movie>movies = new List<Movie>();
            
            while (dbReader.Read())
            {
                movies.Add(
                    new Movie(
                        Convert.ToInt32(dbReader["id"]),
                        Convert.ToBoolean(dbReader["active"].ToString()),
                        dbReader["title"].ToString(),
                        Convert.ToInt32(dbReader["playLength"].ToString()),
                        dbReader["actors"].ToString(),
                        Convert.ToInt32(dbReader["minAge"].ToString()),
                        dbReader["description"].ToString(),
                        dbReader["rating"].ToString(),
                        dbReader["director"].ToString(),
                        dbReader["genre"].ToString(),
                        dbReader["language"].ToString(),
                        Convert.ToDecimal(dbReader["price"].ToString()),
                        dbReader["licencee"].ToString(),
                        Convert.ToDateTime(dbReader["rentalDateStart"]),
                        Convert.ToDateTime(dbReader["rentalDateEnd"])
                    )
                );
            }
           
            dbReader.Close();
            dbCmd.Connection.Close();

            return movies;
        }

        public int InsertMovie(Movie m)
        {
            string sql = "INSERT INTO Movie (active, title, playLength, actors, minAge, description, rating, director, genre, language, price, licencee, rentalDateStart, rentalDateEnd) Values ('" +
                m.Active + "', '" +
                m.Title + "', " +
                m.PlayLength + ", '" +
                m.Actor + "', " +
                m.MinAge + ", '" +
                m.Description + "', '" +
                m.Rating + "', '" +
                m.Director + "', '" +
                m.Genre + "', '" +
                m.Language + "', " +
                m.Price + ", '" +
                m.Licencee + "', " +
                "CAST('" + m.RentalDateStart.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime), " +
                "CAST('" + m.RentalDateEnd.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime))";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int UpdateMovie(Movie m)
        {
            string sql = "UPDATE Movie SET " + 
                "active = '" + m.Active + "', " +
                "title = '" + m.Title + "', " +
                "playLength = " + m.PlayLength + ", " +
                "actor = '" + m.Actor + "', " +
                "minAge = " + m.MinAge + ", " +
                "description = '" + m.Description + "', " +
                "rating = '" + m.Rating + "', " +
                "director = '" + m.Director + "', " +
                "genre = '" + m.Genre + "', " +
                "language = '" + m.Language + "', " +
                "price = " + m.Price + ", " +
                "licencee = '" + m.Licencee + "', " +
                "rentalDateStart = CAST('" + m.RentalDateStart.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime), " +
                "rentalDateEnd = CAST('" + m.RentalDateEnd.ToString("yyyy'-'MM'-'dd") + "' AS smalldatetime) " +
                "WHERE id = " + m.Id + ";";
            
            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int DeleteMovie(int id)
        {
            string sql = "DELETE FROM Movie WHERE id = " + id + ";";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public List<Movie> FindMovies(string input, string category)
        {
            string sql = "";
            if (category.Equals("all"))
            {
                sql ="SELECT * FROM Movie WHERE actors LIKE '%" + input + "%' " + 
                "OR title LIKE '%" + input + "%' OR description LIKE '%" + input + "%' " + 
                "OR director LIKE '%" + input + "%'  OR genre LIKE '%" + input + "%' " +
                " OR language LIKE '%" + input + "%' OR active LIKE '%" + input + "%' " +
                "ORDER BY id ASC;";
            }
            else
            {
                sql = "SELECT * FROM Movie WHERE " + category + " LIKE '%" + input + "%' ORDER BY id ASC;";
            }
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            List<Movie>movies = new List<Movie>();

            while (dbReader.Read())
                movies.Add(
                    new Movie(
                        Convert.ToInt32(dbReader["id"].ToString()),
                        Convert.ToBoolean(dbReader["active"].ToString()),
                        dbReader["title"].ToString(),
                        Convert.ToInt32(dbReader["playLength"].ToString()),
                        dbReader["actors"].ToString(),
                        Convert.ToInt32(dbReader["minAge"].ToString()),
                        dbReader["description"].ToString(),
                        dbReader["rating"].ToString(),
                        dbReader["director"].ToString(),
                        dbReader["genre"].ToString(),
                        dbReader["language"].ToString(),
                        Convert.ToDecimal(dbReader["price"].ToString()),
                        dbReader["licencee"].ToString(),
                        Convert.ToDateTime(dbReader["rentalDateStart"]),
                        Convert.ToDateTime(dbReader["rentalDateEnd"])
                    )
                );
            
            dbReader.Close();
            dbCmd.Connection.Close();
            return movies;
        }
    }
}
